﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Web;
using System.Data.OracleClient;
using System.Xml;
using System.IO;
using System.Collections.Generic;

namespace WFDAL
{
    public enum MySqlType
    {
        insert,
        update,
        delete,
        select,
        max
    }
    public class OracleCommand
    {

      

        //public bool ExecuteProcedure(string p_prcName, OleDbParameterCollection p_Param)
        //{
        //    DataService2.PubDAC.IData iData = this.GetConnection();
        //    DataService2.PubDAC.DataParam dp;//=new DataService2.PubDAC.DataParam();
        //    DataService2.PubDAC.DataParam[] dps = new DataService2.PubDAC.DataParam[p_Param.Count];
        //    for (int i = 0; i < p_Param.Count; i++)
        //    {
        //        dps[i] = this.TransDBType(p_Param[i]);
        //    }
        //    DataService2.PubDAC.DataParamCol dc = new DataService2.PubDAC.DataParamCol(dps);
        //    int res = iData.RetrieveByProc(p_prcName, dc);
        //    if (res == -1)
        //        return false;
        //    else
        //        return true;
        //}

        //public bool ExecuteProcedure(string p_prcName, OleDbParameter[] p_Param)
        //{
        //    DataService2.PubDAC.IData iData = this.GetConnection();
        //    DataService2.PubDAC.DataParam dp;//=new DataService2.PubDAC.DataParam();
        //    DataService2.PubDAC.DataParam[] dps = new DataService2.PubDAC.DataParam[p_Param.Length];
        //    for (int i = 0; i < p_Param.Length; i++)
        //    {
        //        dps[i] = this.TransDBType(p_Param[i]);
        //    }
        //    DataService2.PubDAC.DataParamCol dc = new DataService2.PubDAC.DataParamCol(dps);
        //    int res = iData.RetrieveByProc(p_prcName, dc);
        //    if (res != -1)
        //        return true;
        //    else
        //        return false;
        //}
        public string BuildSql(MySqlType o, string tableName, Dictionary<int, string> columnDic, Dictionary<string, string> inputDic, Dictionary<string, string> whereDic)
        {
            string sql = "";
            string sqlCol = "";
            string sqlVal = "";
            string sqlStatm = "";
            string sqlWhe = "";
            if (((o == MySqlType.update) || (o == MySqlType.delete)) && (whereDic.Count == 0))
            {
                return "条件不可以为空";
            }
            switch (o)
            {
                case MySqlType.insert:
                    foreach (KeyValuePair<string, string> kp in inputDic)
                    {
                        sqlCol = sqlCol + (kp.Key + ",");
                        sqlVal = sqlVal + (" '" + kp.Value + "', ");
                    }
                    sqlCol = sqlCol.Remove(sqlCol.Length - 1, 1);
                    sqlVal = sqlVal.Remove(sqlVal.Length - 2, 2);
                    sql = string.Format(" insert into {0} ({1}) values({2})", tableName, sqlCol, sqlVal);
                    break;
                case MySqlType.update:
                    foreach (KeyValuePair<string, string> kp in inputDic)
                    {
                        sqlStatm = sqlStatm + string.Format(" {0}='{1}' ,", kp.Key, kp.Value);
                    }
                    foreach (KeyValuePair<string, string> kp in whereDic)
                    {
                        sqlWhe = sqlWhe + string.Format(" and  {0}='{1}'", kp.Key, kp.Value);
                    }
                    sqlStatm = sqlStatm.Remove(sqlStatm.Length - 1, 1);
                    sql = string.Format(" update   {0} set  {1} where 1= 1  {2}", tableName, sqlStatm, sqlWhe);
                    break;
                case MySqlType.delete:
                    foreach (KeyValuePair<string, string> kp in whereDic)
                    {
                        sqlWhe = sqlWhe + string.Format(" {0}='{1}'", kp.Key, kp.Value);
                    }
                    sqlWhe = sqlWhe.Remove(sqlWhe.Length - 1, 1);
                    sql = string.Format("delete {0} where {1}", tableName, sqlWhe);
                    break;
                case MySqlType.select:
                    foreach (KeyValuePair<int, string> kp in columnDic)
                    {
                        if (kp.Value != "")
                            sqlCol = sqlCol + string.Format(" {0} ,", kp.Value);
                    }
                    if (sqlCol == null) sqlCol = "*";
                    else
                        sqlCol = sqlCol.Remove(sqlCol.Length - 1, 1);
                    foreach (KeyValuePair<string, string> kp in whereDic)
                    {
                        if (kp.Value != "")
                            sqlWhe = sqlWhe + string.Format(" and  {0}='{1}'", kp.Key, kp.Value);
                    }
                    //sqlWhe = sqlWhe.Remove(sqlWhe.Length - 1, 1);
                    sql = string.Format("select {2} From {0} where 1=1 {1} ", tableName, sqlWhe, sqlCol);
                    break;
                case MySqlType.max:
                    foreach (KeyValuePair<int, string> kp in columnDic)
                    {
                        if (kp.Value != "")
                            sqlCol = sqlCol + string.Format(" {0} ,", kp.Value);
                    }
                    sqlCol = sqlCol.Remove(sqlCol.Length - 1, 1);
                    sql = string.Format("select max({1}) From {0} where 1=1 ", tableName, sqlCol);
                    break;
                default:
                    break;
            }
            return sql;
        }
        /// <summary>
        /// 呼叫後台程式執行
        /// </summary>
        /// <remarks>
        ///		<para>[實做步驟及說明]
        ///		      1. 調用GetConnection()實例化數據庫連接
        ///		      2. 調用iData.RetrieveByProc(p_prcName,p_dc)執行後台程式
        ///			注：調用此方法時p_dc方法的使用如下

        ///		      	DataService2.PubDAC.DataParam[] dps=new DataService2.PubDAC.DataParam[2];
        ///		      	dps[0] = new DataService2.PubDAC.InputDataParam("p_ApplyID",p_ApplyID,System.Data.OracleClient.OracleType.VarChar);
        ///		      	dps[1] = new DataService2.PubDAC.OutputDataParam("p_archive",System.Data.OracleClient.OracleType.Char,1);
        ///		      	dps[1] = new DataService2.PubDAC.InputOutputDataParam("p_archive",v_FindArchive,System.Data.OracleClient.OracleType.Char,1);
        ///		      	DataService2.PubDAC.DataParamCol dc=new DataService2.PubDAC.DataParamCol(dps);
        ///			  3. 返回布爾型態的結果	
        ///		</para>
        /// </remarks>
        /// <param name="p_prcName"></param>
        /// <param name="p_dc"></param>
        /// <returns></returns>
        public bool ExecuteProcedure(string p_prcName, DataService2.PubDAC.DataParamCol p_dc)
        {
            try
            {
                WriteTextPro(p_prcName, p_dc);
                DataService2.PubDAC.IData iData = this.GetConnection();
                int res = iData.RetrieveByProc(p_prcName, p_dc);
                if (res != -1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //public bool ExecuteProcedure_net(string p_prcName, OracleParameter[] p_prcParams, out DataSet ds)
        //{
        //    try
        //    {
        //        WriteTextPro(p_prcName, p_prcParams);
        //        string strConn = ConfigurationSettings.AppSettings["wf_DB"].ToString();
        //        System.Data.OracleClient.OracleConnection oconn = new System.Data.OracleClient.OracleConnection(strConn);
        //        oconn.Open();
        //        System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
        //        cmd.CommandType = CommandType.StoredProcedure;
        //        cmd.Connection = oconn;
        //        cmd.CommandText = p_prcName;
        //        for (int i = 0; i < p_prcParams.Length; i++)
        //            cmd.Parameters.Add(p_prcParams[i]);

        //        DataSet ds1 = new DataSet();
        //        OracleDataAdapter oda = new OracleDataAdapter(cmd);
        //        oda.Fill(ds1);
        //        ds = ds1;
        //        cmd.Dispose();
        //        cmd.Clone();
        //    }
        //    catch (Exception ex)
        //    {
        //        ds = null;
        //        return false;
        //    }
        //    return true;
        //}

        /// <summary>
        /// 將原來呼叫OLEDB.NET執行存儲過程的方法轉變為
        /// 執行DataService層的Oracle.Net的方法執行存儲過程

        /// </summary>
        /// <param name="p_ProcName">存儲過程名稱</param>
        /// <param name="p_Param">OleDb型的參數列表</param>
        /// <returns></returns>
        //public DataTable ExecuteDataTableProcedure(string p_ProcName, OleDbParameter[] p_Param)
        //{
        //    DataService2.PubDAC.IData iData = this.GetConnection();
        //    DataService2.PubDAC.DataParam dp;//=new DataService2.PubDAC.DataParam();
        //    DataService2.PubDAC.DataParam[] dps = new DataService2.PubDAC.DataParam[p_Param.Length + 1];
        //    DataSet ds = new DataSet();
        //    for (int i = 0; i < p_Param.Length; i++)
        //    {
        //        dps[i] = this.TransDBType(p_Param[i]);
        //    }
        //    dps[p_Param.Length] = new DataService2.PubDAC.OutputDataParam("refcur", System.Data.OracleClient.OracleType.Cursor);
        //    DataService2.PubDAC.DataParamCol dc = new DataService2.PubDAC.DataParamCol(dps);
        //    int res = iData.RetrieveByProc(p_ProcName, dc, out ds);
        //    if (ds != null && ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}
        /// <summary>
        /// 呼叫DataService層執行存儲過程並返回數據集

        /// </summary>
        /// <param name="p_ProcName">存儲過程名稱</param>
        /// <param name="p_dc">Oracle.Net型的參數列表</param>
        /// <returns></returns>
        public DataTable ExecuteDataTableProcedure(string p_ProcName, DataService2.PubDAC.DataParamCol p_dc)
        {
            WriteTextPro(p_ProcName, p_dc);
            DataService2.PubDAC.IData iData = this.GetConnection();
            DataSet ds = new DataSet();
            int res = iData.RetrieveByProc(p_ProcName, p_dc, out ds);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public DataSet ExecuteDataSetProcedure(string p_ProcName, DataService2.PubDAC.DataParamCol p_dc)
        {
            WriteTextPro(p_ProcName, p_dc);
            DataService2.PubDAC.IData iData = this.GetConnection();
            DataSet ds = new DataSet();
            int res = iData.RetrieveByProc(p_ProcName, p_dc, out ds);
            return ds;
        }

        public DataService2.PubDAC.IData GetConnection()
        {
            DataService2.PubDAC.IData iData = null;
            DataService2.PubDAC.DataParamCol parCol = new DataService2.PubDAC.DataParamCol();
            string dbtype = System.Configuration.ConfigurationManager.AppSettings["WF_TP"].Trim();
            string cnStr = System.Configuration.ConfigurationManager.AppSettings["WF_DB"].Trim();
            if (dbtype == "1") iData = new DataService2.MsSQL.MsSqlData(cnStr);//ms-sql;
            if (dbtype == "2") iData = new DataService2.OleDb.OleDbData(cnStr);//oledb database..;
            if (dbtype == "4") iData = new DataService2.OracleDb.OracleDbData(cnStr);//Oracle client by ms provided..
            if (dbtype == "3") iData = new DataService2.MsOraClient.MsOraDbData(cnStr);//Oracle client  by Oracle provided....

            return iData;
        }

        public static String GetConnectionString()
        {
            return System.Configuration.ConfigurationManager.AppSettings["WF_DB"].Trim();
        }

        /// <summary>
        /// 取得歷史數據庫的DataBase Info 60.66
        /// </summary>
        /// <returns></returns>
        public DataService2.PubDAC.IData GetConnectionHis()
        {
            //DataService2.PubDAC.IData iData = null;
            //DataService2.PubDAC.DataParamCol parCol = new DataService2.PubDAC.DataParamCol();
            //string dbtype = System.Configuration.ConfigurationManager.AppSettings["WF_TP"].Trim();
            //string cnStr = System.Configuration.ConfigurationManager.AppSettings["WF_HIS_DB"].Trim();
            //if (dbtype == "1") iData = new DataService2.MsSQL.MsSqlData(cnStr);//ms-sql;
            //if (dbtype == "2") iData = new DataService2.OleDb.OleDbData(cnStr);//oledb database..;
            //if (dbtype == "4") iData = new DataService2.OracleDb.OracleDbData(cnStr);//Oracle client by ms provided..
            //if (dbtype == "3") iData = new DataService2.MsOraClient.MsOraDbData(cnStr);//Oracle client  by Oracle provided....

            //return iData;
            return GetConnectionByConfig("WF_HIS");
        }
        public DataService2.PubDAC.IData GetConnectionByConfig(string p_ConfigName)
        {
            DataService2.PubDAC.IData iData = null;
            DataService2.PubDAC.DataParamCol parCol = new DataService2.PubDAC.DataParamCol();
            string dbtype = System.Configuration.ConfigurationManager.AppSettings[p_ConfigName + "_TP"].Trim();
            string cnStr = System.Configuration.ConfigurationManager.AppSettings[p_ConfigName + "_db"].Trim();
            if (dbtype == "1") iData = new DataService2.MsSQL.MsSqlData(cnStr);//ms-sql;
            if (dbtype == "2") iData = new DataService2.OleDb.OleDbData(cnStr);//oledb database..;
            if (dbtype == "4") iData = new DataService2.OracleDb.OracleDbData(cnStr);//Oracle client by ms provided..
            if (dbtype == "3") iData = new DataService2.MsOraClient.MsOraDbData(cnStr);//Oracle client  by Oracle provided....

            return iData;
        }


        //public DataService2.PubDAC.DataParam TransDBType(OleDbParameter p_olepm)
        //{
        //    DataService2.PubDAC.DataParam dp;
        //    System.Data.OracleClient.OracleType otype = new System.Data.OracleClient.OracleType();
        //    if (p_olepm.OleDbType == OleDbType.Char) otype = System.Data.OracleClient.OracleType.Char;
        //    else if (p_olepm.OleDbType == OleDbType.VarChar) otype = System.Data.OracleClient.OracleType.VarChar;
        //    else if (p_olepm.OleDbType == OleDbType.Date) otype = System.Data.OracleClient.OracleType.DateTime;
        //    else if (p_olepm.OleDbType == OleDbType.Numeric) otype = System.Data.OracleClient.OracleType.Number;

        //    if (p_olepm.Direction == ParameterDirection.Input)
        //    {
        //        dp = new DataService2.PubDAC.InputDataParam(p_olepm.ParameterName, p_olepm.Value, otype, p_olepm.Size);
        //        //dp.ParType=otype;
        //        return dp;
        //    }
        //    else if (p_olepm.Direction == ParameterDirection.Output)
        //    {
        //        //if(procpara.Split('.')[0].ToString().Trim()!="5")
        //        //	parCol.Add(new DataService2.PubDAC.OutputDataParam(xmlProcPras[0].ChildNodes[a].Name.Trim(),otype,System.Int16.Parse(procpara.Split('.')[1])));
        //        //else
        //        dp = new DataService2.PubDAC.OutputDataParam(p_olepm.ParameterName, otype);
        //        return dp;
        //    }
        //    else if (p_olepm.Direction == ParameterDirection.InputOutput)
        //    {
        //        dp = new DataService2.PubDAC.InputOutputDataParam(p_olepm.ParameterName, p_olepm.Value, otype, p_olepm.Size);
        //        return dp;
        //    }
        //    return null;
        //}

        public bool ExecuteSQL(string p_strSQL)
        {
            WriteText(p_strSQL, "a.txt");
            DataService2.PubDAC.IData iData = this.GetConnection();
            int res = iData.RetrieveByCmdTxt(p_strSQL);
            if (res != -1)
                return true;
            else
                return false;
        }


        #region 查詢返回

        public string QuerySclare(string p_strSQL)
        {
            WriteText(p_strSQL, "a.txt");
            object objReturn = null;
            DataService2.PubDAC.IData iData = this.GetConnection();
            iData.RetrieveByCmdTxt(p_strSQL, out objReturn);
            if (objReturn != null)
                return objReturn.ToString();
            else
                return "";
        }
        /// <summary>
        /// 執行查詢，返回DataTable
        /// </summary>
        /// <param name="p_strSQL">查詢語句</param>
        /// <returns>DataTable</returns>
        public DataTable Query(string p_strSQL)
        {
            WriteText(p_strSQL, "a.txt");
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            DataService2.PubDAC.IData iData = this.GetConnection();
            iData.RetrieveByCmdTxt(p_strSQL, out ds);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 從歷史數據庫60.66查詢資料。

        /// </summary>
        /// <param name="p_strSQL"></param>
        /// <returns></returns>
        public DataTable QueryHis(string p_strSQL)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            DataService2.PubDAC.IData iData = this.GetConnectionHis();
            iData.RetrieveByCmdTxt(p_strSQL, out ds);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public DataSet QueryDataSet(string p_StrSQL)
        {

            DataSet ds = new DataSet();
            DataService2.PubDAC.IData iData = this.GetConnection();
            iData.RetrieveByCmdTxt(p_StrSQL, out ds);
            WriteText(p_StrSQL, "a.txt");
            if (ds != null)
                return ds;
            else
                return null;
        }

        /// <summary>
        /// 快捷的查询方法
        /// </summary>
        /// <param name="p_Table"></param>
        /// <param name="p_Where"></param>
        /// <returns></returns>
        public DataTable QueryByWhere(string p_Table, string p_Where)
        {
            string sql = "select * from " + p_Table + " where " + p_Where;
            return Query(sql);
        }
        public DataSet QueryDsByWhere(string p_Table, string p_Where)
        {
            string sql = "select * from " + p_Table + " where " + p_Where;
            return this.QueryDataSet(sql);
        }

        /// <summary>
        /// 移除SQL中的特殊字元，防止SQL注入
        /// 待扩充
        /// </summary>
        /// <param name="inputValue"></param>
        /// <returns></returns>
        public string RemoveSqlInject(string inputValue)
        {
            return inputValue.Replace("'", "''");
        }

        public void WriteTextPro(string p_prcname, OracleParameter[] dc)
        {
            if (System.Configuration.ConfigurationManager.AppSettings["WriteDBError"] == null)
                return;
            if (System.Configuration.ConfigurationManager.AppSettings["WriteDBError"].ToString().Trim() == "0")
                return;
            string paraS = "";
            string retVal = "";
            try
            {
                foreach (OracleParameter par in dc)
                {
                    paraS += string.Format("{0}={1},", par.ParameterName, par.Value);
                }
                retVal = string.Format("{0}({1})", p_prcname, paraS);
                WriteText(retVal, "a.txt");
            }
            catch (Exception ex)
            {

            }
        }

        public void WriteTextPro(string p_prcname, DataService2.PubDAC.DataParamCol dc)
        {
            if (System.Configuration.ConfigurationManager.AppSettings["WriteDBError"] == null)
                return;
            if (System.Configuration.ConfigurationManager.AppSettings["WriteDBError"].ToString().Trim() == "0")
                return;
            string paraS = "";
            string retVal = "";
            try
            {
                foreach (DataService2.PubDAC.DataParam par in dc)
                {
                    paraS += string.Format("{0}={1},", par.ParName, par.ParValue);
                }
                retVal = string.Format("{0}({1})", p_prcname, paraS);
                WriteText(retVal, "a.txt");
            }
            catch (Exception ex)
            {

            }
        }

        public void WriteText(string _str, string p_filleName)
        {
            string files = DateTime.Now.ToString("yyyyMMdd");
            if (System.Configuration.ConfigurationManager.AppSettings["WriteDBError"] == null)
                return;
            if (System.Configuration.ConfigurationManager.AppSettings["WriteDBError"].ToString().Trim() == "0")
                return;
            try
            {
                DirectoryInfo directoryInfo = new DirectoryInfo(System.Web.HttpContext.Current.Request.MapPath("~"));
                directoryInfo.CreateSubdirectory("inc/tree/sqllogs");
                string chgtime = DateTime.Now.ToString("yyyyMMddhh24miss");
                string logCont = string.Format("---------------------execute_sql---------------------\r\n{0}\r\n---------------------execute_time---------------------\r\n{1}\r\n", _str, chgtime);
                DirectoryInfo info = new DirectoryInfo(System.Web.HttpContext.Current.Request.MapPath("~/Inc/tree/sqllogs/" + files + ".txt"));
                System.IO.FileStream fs = new System.IO.FileStream(info.FullName, System.IO.FileMode.Append, System.IO.FileAccess.Write);
                System.IO.StreamWriter sw = new System.IO.StreamWriter(fs);
                try
                {
                    sw.WriteLine(logCont);
                    sw.Close();
                }
                catch (Exception ex)
                {
                    System.Web.HttpContext.Current.Response.Write(ex.ToString());
                    System.Web.HttpContext.Current.Response.End();
                }
                finally
                {
                    sw.Close();
                }
            }
            catch (Exception ex) { };

        }



        public OracleDataReader QueryReader(string p_SQL)
        {
            System.Data.IDataReader odr;
            DataService2.PubDAC.IData iData = this.GetConnection();
            iData.RetrieveByCmdTxt(p_SQL, out odr);
            if (odr != null)
                return (System.Data.OracleClient.OracleDataReader)odr;
            else
                return null;
        }

        #endregion


    }
}
